import json
from tkinter import messagebox
from typing import List

import pandas as pd

from ..models.data_source import SourceType
from ..utils.logger import logger

# 条件导入数据库模块
try:
    import pymysql

    MYSQL_AVAILABLE = True
except ImportError:
    MYSQL_AVAILABLE = False
    logger.warning("PyMySQL模块未安装，MySQL数据库功能将不可用")

try:
    import oracledb

    ORACLE_AVAILABLE = True
except ImportError:
    ORACLE_AVAILABLE = False
    logger.warning("oracledb模块未安装，Oracle数据库功能将不可用")


class DataLoader:
    """数据加载器"""

    def __init__(
        self, source_type: SourceType, source_path: str, db_config: dict = None
    ):
        """初始化
        Args:
            source_type: 数据源类型
            source_path: 数据源路径（对于数据库类型，这里是SQL语句）
            db_config: 数据库配置信息
        """
        self.type = source_type
        self.source_path = source_path
        self.db_config = db_config or {}

    def get_fields(self) -> List[str]:
        """获取数据源字段列表"""
        try:
            if self.type == SourceType.DATABASE:
                return self._get_database_fields()
            elif self.type == SourceType.CSV:
                return self._get_csv_fields()
            elif self.type == SourceType.EXCEL:
                return self._get_excel_fields()
            elif self.type == SourceType.JSON:
                return self._get_json_fields()
            else:
                logger.warning(f"不支持的数据源类型: {self.type}")
                return []

        except Exception as e:
            logger.error(f"获取字段列表失败: {str(e)}", exc_info=True)
            return []

    def _get_database_fields(self) -> List[str]:
        """获取数据库字段列表"""
        if not self.db_config or not self.source_path:
            logger.warning("数据库配置或SQL语句为空")
            return []

        db_type = self.db_config.get("type", "").lower()
        try:
            if db_type == "mysql":
                if not MYSQL_AVAILABLE:
                    raise ImportError(
                        "未安装 PyMySQL 模块，请先安装：pip install PyMySQL"
                    )
                return self._get_mysql_fields()
            elif db_type == "oracle":
                if not ORACLE_AVAILABLE:
                    raise ImportError(
                        "未安装 cx_Oracle 模块或 Oracle 客户端，请按照以下步骤安装：\n"
                        "1. 安装 cx_Oracle：pip install cx-Oracle\n"
                        "2. 下载并安装 Oracle 客户端：\n"
                        "   - 访问：https://www.oracle.com/database/technologies/instant-client/downloads.html\n"
                        "   - 选择对应的操作系统和版本\n"
                        "   - 下载 Basic Package\n"
                        "   - 解压到本地目录\n"
                        "   - 将解压目录添加到系统环境变量 PATH 中\n"
                        "3. 重启应用"
                    )
                return self._get_oracle_fields()
            else:
                raise ValueError(f"不支持的数据库类型: {db_type}")

        except ImportError as e:
            logger.error(f"缺少必要的数据库驱动: {str(e)}")
            messagebox.showerror("错误", str(e))
            return []
        except Exception as e:
            logger.error(f"获取数据库字段失败: {str(e)}", exc_info=True)
            messagebox.showerror("错误", f"获取数据库字段失败: {str(e)}")
            return []

    def _get_mysql_fields(self) -> List[str]:
        """获取MySQL字段列表"""
        if not MYSQL_AVAILABLE:
            logger.error("PyMySQL模块未安装，无法使用MySQL功能")
            return []

        conn = None
        cursor = None
        try:
            # 连接MySQL数据库
            conn = pymysql.connect(
                host=self.db_config.get("host", "localhost"),
                port=int(self.db_config.get("port", 3306)),
                user=self.db_config.get("username", "root"),
                password=self.db_config.get("password", ""),
                database=self.db_config.get("database", ""),
            )
            cursor = conn.cursor()

            # 执行SQL语句，但只获取0行数据以获取字段信息
            modified_sql = f"SELECT * FROM ({self.source_path}) AS temp LIMIT 0"
            cursor.execute(modified_sql)

            # 获取字段名列表
            return [desc[0] for desc in cursor.description]

        finally:
            if cursor:
                cursor.close()
            if conn:
                conn.close()

    def _get_oracle_fields(self) -> List[str]:
        """获取Oracle字段列表"""
        if not ORACLE_AVAILABLE:
            logger.error("oracledb模块未安装，无法使用Oracle功能")
            return []

        conn = None
        cursor = None
        try:
            # 初始化Oracle客户端
            self._init_oracle_client()

            # 根据模式创建连接
            if self.db_config.get("mode") == "thick":
                dsn = f"{self.db_config['host']}:{self.db_config['port']}/{self.db_config['database']}"
                conn = oracledb.connect(
                    user=self.db_config["username"],
                    password=self.db_config["password"],
                    dsn=dsn,
                )
            else:
                # thin模式连接
                dsn = f"{self.db_config['host']}:{self.db_config['port']}/{self.db_config['database']}"
                conn = oracledb.connect(
                    user=self.db_config["username"],
                    password=self.db_config["password"],
                    dsn=dsn,
                    config_dir=None,
                    thin=True,
                )

            # 创建游标
            cursor = conn.cursor()

            # 执行SQL语句，但只获取0行数据以获取字段信息
            modified_sql = f"SELECT * FROM ({self.source_path}) WHERE ROWNUM < 1"
            cursor.execute(modified_sql)

            # 获取字段名列表
            return [desc[0] for desc in cursor.description]

        except Exception as e:
            logger.error(f"获取Oracle字段失败: {str(e)}", exc_info=True)
            messagebox.showerror("错误", f"获取Oracle字段失败: {str(e)}")
            return []

        finally:
            if cursor:
                cursor.close()
            if conn:
                conn.close()

    def _get_csv_fields(self) -> List[str]:
        """获取CSV字段列表"""
        df = pd.read_csv(self.source_path, nrows=0)
        return list(df.columns)

    def _get_excel_fields(self) -> List[str]:
        """获取Excel字段列表"""
        df = pd.read_excel(self.source_path, nrows=0)
        return list(df.columns)

    def _get_json_fields(self) -> List[str]:
        """获取JSON字段列表"""
        with open(self.source_path, "r", encoding="utf-8") as f:
            data = json.load(f)
            if isinstance(data, list) and len(data) > 0:
                return list(data[0].keys())
            elif isinstance(data, dict):
                return list(data.keys())
            else:
                return []

    def load_data(self) -> pd.DataFrame:
        """加载数据"""
        try:
            if self.type == SourceType.DATABASE:
                # 数据库数据加载
                if not self.db_config:
                    logger.error("数据库配置为空")
                    return pd.DataFrame()

                db_type = self.db_config["type"].lower()
                if db_type == "mysql":
                    import pymysql

                    conn = pymysql.connect(
                        host=self.db_config["host"],
                        port=self.db_config["port"],
                        user=self.db_config["username"],
                        password=self.db_config["password"],
                        database=self.db_config["database"],
                    )
                elif db_type == "oracle":
                    import oracledb

                    # 初始化Oracle客户端
                    self._init_oracle_client()

                    if self.db_config.get("mode") == "thick":
                        dsn = f"{self.db_config['host']}:{self.db_config['port']}/{self.db_config['database']}"
                        conn = oracledb.connect(
                            user=self.db_config["username"],
                            password=self.db_config["password"],
                            dsn=dsn,
                        )
                    else:
                        # thin模式连接
                        dsn = f"{self.db_config['host']}:{self.db_config['port']}/{self.db_config['database']}"
                        conn = oracledb.connect(
                            user=self.db_config["username"],
                            password=self.db_config["password"],
                            dsn=dsn,
                            config_dir=None,
                            thin=True,
                        )

                # 执行查询
                df = pd.read_sql(self.source_path, conn)
                conn.close()

                # 打印加载的数据信息
                logger.info(
                    f"从数据库加载数据: \n"
                    f"类型: {db_type}\n"
                    f"行数: {len(df)}\n"
                    f"列: {df.columns.tolist()}\n"
                    f"前5行数据:\n{df.head()}"
                )
                return df

            elif self.type == SourceType.CSV:
                df = pd.read_csv(self.source_path)
            elif self.type == SourceType.EXCEL:
                df = pd.read_excel(self.source_path)
            elif self.type == SourceType.JSON:
                with open(self.source_path, "r", encoding="utf-8") as f:
                    data = json.load(f)
                    if isinstance(data, list):
                        # 预处理 JSON 数据，确保数值类型正确
                        for item in data:
                            for key, value in item.items():
                                if (
                                    isinstance(value, (int, float))
                                    and float(value).is_integer()
                                ):
                                    item[key] = int(value)
                        df = pd.DataFrame(data)
                    elif isinstance(data, dict):
                        # 预处理单个对象的情况
                        for key, value in data.items():
                            if (
                                isinstance(value, (int, float))
                                and float(value).is_integer()
                            ):
                                data[key] = int(value)
                        df = pd.DataFrame([data])
                    else:
                        return pd.DataFrame()

            # 修正数值类型：对所有数值列进行检查和转换
            for col in df.columns:
                logger.info(f"检查列 {col} 的数据类型: {df[col].dtype}")
                if pd.api.types.is_numeric_dtype(df[col]):
                    logger.info(f"列 {col} 是数值类型")
                    # 检查是否所有非空值都是整数
                    non_null_values = df[col].dropna()
                    if len(non_null_values) > 0:
                        try:
                            # 检查是否所有值都可以转为整数
                            values_list = list(non_null_values)
                            logger.info(f"列 {col} 的非空值: {values_list}")
                            is_all_int = all(float(x).is_integer() for x in values_list)
                            logger.info(f"列 {col} 是否全为整数: {is_all_int}")
                            if is_all_int:
                                df[col] = df[col].astype(int)
                                logger.info(f"列 {col} 已转换为整数类型")
                        except Exception as e:
                            logger.error(f"列 {col} 转换类型时出错: {str(e)}")
                            continue

            return df

        except Exception as e:
            logger.error(f"加载数据失败: {str(e)}", exc_info=True)
            return pd.DataFrame()

    def _init_oracle_client(self):
        """初始化Oracle客户端"""
        if not ORACLE_AVAILABLE:
            return

        try:
            if (
                self.db_config.get("type", "").lower() == "oracle"
                and self.db_config.get("mode") == "thick"
                and "oracle_client_dir" in self.db_config
            ):

                # 尝试初始化Oracle客户端
                try:
                    oracledb.init_oracle_client(
                        lib_dir=self.db_config["oracle_client_dir"]
                    )
                except Exception as e:
                    # 忽略已经初始化的错误
                    if "OracleClient library has already been initialized" not in str(
                        e
                    ):
                        raise

                logger.info(
                    f"Oracle客户端初始化成功: {self.db_config['oracle_client_dir']}"
                )

        except Exception as e:
            logger.error(f"Oracle客户端初始化失败: {str(e)}", exc_info=True)
            raise
